![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
The information contained in the internal tables referenced by these views is used by the optimizer to make decisions about which execution plan to take. The decision about which execution plan to take is also based on information about the size of the object and the data contained in the object. Some of the information contained in these tables is presented in Tables 27.2, 27.3, 27.4, and 27.5.
With this information, the optimizer can more precisely determine the optimal execution path based on data about your specific system. When you run the ANALYZE command with the ESTIMATE STATISTICS option, many of these values are estimates rather than actual computed results. HintsYou can use hints to inform the optimizer of any special facts you know about the data or the SQL statements that may affect the execution plan. By using hints, you indicate that the SQL statement may be more efficient by using a certain execution plan (such as a full-table scan or increased parallelism). Hints are detailed in Chapter 30, Using Hints. SummaryWhen SQL statements are executed, the Oracle optimizer determines the execution plan based on the available data. The Oracle optimizer uses the optimization approach specified in the initialization parameters to determine the execution plan, as you have seen in this chapter. The effectiveness of the execution plan depends on the optimization method chosen and the availability of good statistics for your database. When using the cost-based optimization approach, the effectiveness of the optimization can be enhanced by including more and better database performance statistics. Regardless of your system configuration, you can override or enhance the optimization approach by using hints, as discussed in Chapter 30. This chapter described the various optimization approaches. I recommend the use of the CHOOSE hint under most conditions. When you specify CHOOSE, Oracle takes advantage of the cost-based optimizer under most situations but uses the rule-based approach when no statistics are available. In Chapter 30, you see how SQL statements can be further enhanced by using hints and information you know about your data and your database layout.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |